#include "db.h"

#define SQLITE3_EXEC(database, sql)                                       \
	{                                                                     \
		char *err_msg = nullptr;                                          \
		const int result_code =                                           \
			sqlite3_exec(database, sql, nullptr, nullptr, &err_msg);      \
		if (result_code != SQLITE_OK)                                     \
		{                                                                 \
			LOG(ERROR) << "SQLite error: " << err_msg;                    \
			sqlite3_free(err_msg);                                        \
			return 0;                                                     \
		}                                                                 \
	}

#define SQLITE3_CALL(sql_stmt)                                             \
	{                                                                      \
		const int result_code = sqlite3_step(sql_stmt);                    \
		sqlite3_reset(sql_stmt);                                           \
		switch (result_code)                                               \
		{                                                                  \
			case SQLITE_OK:                                                \
			case SQLITE_ROW:                                               \
			case SQLITE_DONE:                                              \
				return 1;                                                  \
			default:                                                       \
				LOG(ERROR) << "SQLite error: " <<                          \
					sqlite3_errstr(result_code);                           \
				return 0;                                                  \
		}                                                                  \
	}

namespace OPT
{
	extern bool bFloatDesc;  // 浮点型描述子
	extern bool bFeatureCompress;
	extern bool bCompress;
	extern unsigned descLength;
}

void Db::PrepareSQLStatements()
{
	sql_stmts_.clear();
	string sql;

	// 特征提取相关sql
	sql = "INSERT INTO images(name,group_id,valid) VALUES(?,?,?);";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_WriteImageName_, 0);
	sql_stmts_.push_back(sql_stmt_WriteImageName_);

	sql = "SELECT 1 FROM images WHERE name=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ExistImageName_, 0);
	sql_stmts_.push_back(sql_stmt_ExistImageName_);

	sql = "SELECT image_id FROM images WHERE name=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ReadImageID_, 0);
	sql_stmts_.push_back(sql_stmt_ReadImageID_);

	sql = "SELECT name FROM images WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ReadImageName_, 0);
	sql_stmts_.push_back(sql_stmt_ReadImageName_);

    sql = "UPDATE images SET valid=?,width=?,height=?,fx=?,fy=?,u=?,v=?,gps_lon=?,gps_lat=?,gps_alt=?,gps_x=?,gps_y=?,gps_z=? WHERE image_id=?";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateImageParam_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateImageParam_);

	sql = "SELECT 1 FROM keypoints WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ExistKeypoints_, 0);
	sql_stmts_.push_back(sql_stmt_ExistKeypoints_);

	sql = "INSERT INTO keypoints (image_id,keys_num,keys_data,descs_data) VALUES (?,?,?,?);";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_WriteKeypoints_, 0);
	sql_stmts_.push_back(sql_stmt_WriteKeypoints_);

	sql = "UPDATE keypoints SET keys_num=?,keys_data=?,descs_data=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateKeypoints_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateKeypoints_);

	sql = "SELECT keys_num,keys_data,descs_data FROM keypoints WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ReadKeypoints_, 0);
	sql_stmts_.push_back(sql_stmt_ReadKeypoints_);

	sql = "SELECT gps_lon,gps_lat,gps_alt,gps_x,gps_y,gps_z FROM images WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ReadImageGPS_, 0);
	sql_stmts_.push_back(sql_stmt_ReadImageGPS_);

	sql = "UPDATE images SET gps_lon=?,gps_lat=?,gps_alt=?,gps_x=?,gps_y=?,gps_z=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateImageGPS_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateImageGPS_);

	sql = "UPDATE images SET cx=?,cy=?,cz=?,R=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateImagePose_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateImagePose_);

	sql = "SELECT 1 FROM images WHERE group_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ExistGroupID_, 0);
	sql_stmts_.push_back(sql_stmt_ExistGroupID_);

	sql = "UPDATE images SET fx=?,fy=?,u=?,v=?,k1=?,k2=?,k3=?,k4=?,k5=? WHERE group_id=?";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateInitCams_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateInitCams_);

	sql = "UPDATE images SET fx=?,fy=?,u=?,v=?,k1=?,k2=?,k3=?,k4=?,k5=? WHERE image_id=?";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ResetInitCams_, 0);
	sql_stmts_.push_back(sql_stmt_ResetInitCams_);

	// 特征匹配相关sql
    sql = "SELECT 1 FROM matches WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ExistMatches_, 0);
	sql_stmts_.push_back(sql_stmt_ExistMatches_);

    sql = "INSERT INTO matches (image_id,match_data_num,match_data,model_data_num,model_data) VALUES (?,?,?,?,?);";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_WriteMatches_, 0);
	sql_stmts_.push_back(sql_stmt_WriteMatches_);

	sql = "UPDATE matches SET match_data_num=?,match_data=?,model_data_num=?,model_data=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateMatches_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateMatches_);

	// SfM相关sql
	sql = "UPDATE images SET valid=?,fx=?,fy=?,u=?,v=?,k1=?,k2=?,k3=?,k4=?,k5=?,cx=?,cy=?,cz=?,R=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_WriteSfMResult_, 0);
	sql_stmts_.push_back(sql_stmt_WriteSfMResult_);

	// MVS相关sql
	sql = "SELECT name,group_id,valid,width,height,fx,fy,u,v,k1,k2,k3,k4,k5,cx,cy,cz,R FROM images WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ReadOriginalImageInfo_, 0);
	sql_stmts_.push_back(sql_stmt_ReadOriginalImageInfo_);

	sql = "SELECT 1 FROM images_scaled WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ExistImageScaled_, 0);
	sql_stmts_.push_back(sql_stmt_ExistImageScaled_);

	sql = "INSERT INTO images_scaled (image_id,name,valid,scale,width,height,fx,fy,u,v,cx,cy,cz,R) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_WriteImageScaledParam_, 0);
	sql_stmts_.push_back(sql_stmt_WriteImageScaledParam_);

	sql = "UPDATE images_scaled SET name=?,valid=?,scale=?,width=?,height=?,fx=?,fy=?,u=?,v=?,cx=?,cy=?,cz=?,R=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateImageScaledParam_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateImageScaledParam_);

	sql = "UPDATE images_scaled SET dmin=?,dmax=?,neighbor_num=?,neighbor_data=?,points_num=?,points=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_WriteNeighbors_, 0);
	sql_stmts_.push_back(sql_stmt_WriteNeighbors_);

	sql = "SELECT name,valid,scale,width,height,fx,fy,u,v,cx,cy,cz,R,dmin,dmax,neighbor_num,neighbor_data,points_num,points FROM images_scaled WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ReadImageScaledParam_, 0);
	sql_stmts_.push_back(sql_stmt_ReadImageScaledParam_);

	sql = "SELECT 1 FROM depthmaps WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ExistDepthMap_, 0);
	sql_stmts_.push_back(sql_stmt_ExistDepthMap_);

	sql = "INSERT INTO depthmaps (image_id,valid,num,depthMap,confMap,normalmap) VALUES (?,?,?,?,?,?);";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_WriteDepthMap_, 0);
	sql_stmts_.push_back(sql_stmt_WriteDepthMap_);

	sql = "UPDATE depthmaps SET valid=?,num=?,depthMap=?,confMap=?,normalmap=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateDepthMap_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateDepthMap_);

	sql = "SELECT valid,num,depthMap,confMap,normalmap FROM depthmaps WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ReadDepthMap_, 0);
	sql_stmts_.push_back(sql_stmt_ReadDepthMap_);

	sql = "SELECT 1 FROM maskfilters WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ExistMaskFilter_, 0);
	sql_stmts_.push_back(sql_stmt_ExistMaskFilter_);

	sql = "INSERT INTO maskfilters (image_id,valid,num,maskfilter) VALUES (?,?,?,?);";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_WriteMaskFilter_, 0);
	sql_stmts_.push_back(sql_stmt_WriteMaskFilter_);

	sql = "UPDATE maskfilters SET valid=?,num=?,maskfilter=? WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_UpdateMaskFilter_, 0);
	sql_stmts_.push_back(sql_stmt_UpdateMaskFilter_);

	sql = "SELECT valid,num,maskfilter FROM maskfilters WHERE image_id=?;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_ReadMaskFilter_, 0);
	sql_stmts_.push_back(sql_stmt_ReadMaskFilter_);
}

void Db::FinalizeSQLStatements()
{
	for (const auto& sql_stmt : sql_stmts_)
		sqlite3_finalize(sql_stmt);
}

bool Db::Open(bool over_write)
{
	Close();

	const int result_code = sqlite3_open_v2(db_name.c_str(), &database_, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX, nullptr);
	if (result_code != SQLITE_OK)
	{
		LOG(ERROR) << "SQLite error: " << sqlite3_errstr(result_code);
		return 0;
	}

	SQLITE3_EXEC(database_, "PRAGMA synchronous=OFF");
	SQLITE3_EXEC(database_, "PRAGMA journal_mode=WAL");
	SQLITE3_EXEC(database_, "PRAGMA temp_store=MEMORY");

	if (over_write)
	{
		if (!DeleteTables())
		{
			LOG(ERROR) << "Failed to delete tables";
			return 0;
		}
	}

	if (!CreatTables())
	{
		LOG(ERROR) << "Failed to creat tables";
		return 0;
	}

	PrepareSQLStatements();

	return 1;
}

void Db::Close()
{
	if (database_ != nullptr)
	{
		FinalizeSQLStatements();
		sqlite3_close_v2(database_);
		database_ = nullptr;
	}
}

bool Db::CreatTables()
{
	char* err_msg = nullptr;
	int result_code;
	string sql;

	sql = "CREATE TABLE IF NOT EXISTS images ("
		"image_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
		"name TEXT NOT NULL,"
		"group_id INTEGER,"
		"valid INTEGER,"
		"width INTEGER,"
		"height INTEGER,"
		"fx REAL, fy REAL, u REAL, v REAL,"
		"k1 REAL, k2 REAL, k3 REAL, k4 REAL, k5 REAL,"		
		"cx REAL, cy REAL, cz REAL,"
		"R BLOB,"
        "gps_lon REAL, gps_lat REAL, gps_alt REAL,"
		"gps_x REAL, gps_y REAL, gps_z REAL);";
	SQLITE3_EXEC(database_, sql.c_str());

	sql = "CREATE TABLE IF NOT EXISTS keypoints ("
		"image_id INTEGER PRIMARY KEY NOT NULL,"
		"keys_num INTEGER,"
		"keys_data BLOB,"
		"descs_data BLOB);";
	SQLITE3_EXEC(database_, sql.c_str());

    sql = "CREATE TABLE IF NOT EXISTS matches ("
		"image_id INTEGER PRIMARY KEY NOT NULL,"
		"match_data_num INTEGER,"
		"match_data BLOB,"
		"model_data_num INTEGER,"
		"model_data BLOB);";
	SQLITE3_EXEC(database_, sql.c_str());

    sql = "CREATE TABLE IF NOT EXISTS images_scaled ("
		"image_id INTEGER PRIMARY KEY NOT NULL,"
		"name TEXT NOT NULL,"
		"valid INTEGER,"
		"scale REAL,"
		"width INTEGER,"
		"height INTEGER,"
		"fx REAL, fy REAL, u REAL, v REAL,"		
		"cx REAL, cy REAL, cz REAL,"
		"R BLOB,"
        "dmin REAL,"
		"dmax REAL,"
		"neighbor_num INTEGER,"
		"neighbor_data BLOB,"
		"points_num INTEGER,"
		"points BLOB);";    
    SQLITE3_EXEC(database_, sql.c_str());
    
    sql = "CREATE TABLE IF NOT EXISTS depthmaps ("
		"image_id INTEGER PRIMARY KEY NOT NULL,"
		"valid BOOLEAN,"
		"num INTEGER,"
		"depthmap BLOB,"
		"confmap BLOB,"
		"normalmap BLOB);";
    SQLITE3_EXEC(database_, sql.c_str());

	sql = "CREATE TABLE IF NOT EXISTS maskfilters ("
		"image_id INTEGER PRIMARY KEY NOT NULL,"
		"valid BOOLEAN,"
		"num INTEGER,"
		"maskfilter BLOB);";
    SQLITE3_EXEC(database_, sql.c_str());

	return 1;
}

bool Db::DeleteTables()
{
	SQLITE3_EXEC(database_, "DROP TABLE IF EXISTS images;");
	SQLITE3_EXEC(database_, "DROP TABLE IF EXISTS keypoints;");
	SQLITE3_EXEC(database_, "DROP TABLE IF EXISTS matches;");
	SQLITE3_EXEC(database_, "DROP TABLE IF EXISTS images_scaled;");
	SQLITE3_EXEC(database_, "DROP TABLE IF EXISTS depthmaps;");
	SQLITE3_EXEC(database_, "DROP TABLE IF EXISTS maskfilters;");

	return 1;
}

bool Db::BeginTransaction()
{
	SQLITE3_EXEC(database_, "BEGIN TRANSACTION;");
	return 1;
}

bool Db::CommitTransaction()
{
	SQLITE3_EXEC(database_, "END TRANSACTION;");
	return 1;
}

bool Db::WriteImageName(string name, int group_id, int valid)
{
	sqlite3_bind_text(sql_stmt_WriteImageName_, 1, name.c_str(), static_cast<int>(name.size()), SQLITE_STATIC);
	sqlite3_bind_int64(sql_stmt_WriteImageName_, 2, group_id);
	sqlite3_bind_int64(sql_stmt_WriteImageName_, 3, valid);
	SQLITE3_CALL(sql_stmt_WriteImageName_);
}

bool Db::ExistImageName(string name)
{
	sqlite3_bind_text(sql_stmt_ExistImageName_, 1, name.c_str(), static_cast<int>(name.size()), SQLITE_STATIC);
	const int result_code = sqlite3_step(sql_stmt_ExistImageName_);
	sqlite3_reset(sql_stmt_ExistImageName_);

	if (result_code != SQLITE_ROW)
		return 0;
	else
		return 1;
}

bool Db::ExistGroupID(int group_id)
{
	int result_code;

	sqlite3_bind_int64(sql_stmt_ExistGroupID_, 1, group_id);
	result_code = sqlite3_step(sql_stmt_ExistGroupID_);
	sqlite3_reset(sql_stmt_ExistGroupID_);

	if (result_code != SQLITE_ROW)
		return 0;
	else
		return 1;
}

bool Db::ReadAllImageID(vector<unsigned>& IDs)
{
	IDs.clear();

	string sql = "SELECT image_id FROM images;";
	sqlite3_stmt* sql_stmt;
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);
	while (sqlite3_step(sql_stmt) == SQLITE_ROW)
		IDs.push_back(sqlite3_column_int64(sql_stmt, 0));
	
	sqlite3_finalize(sql_stmt);
	return 1;
}

bool Db::ReadAllImageIDName(vector<unsigned>& IDs, vector<string>& names)
{
	IDs.clear();
	names.clear();

	string sql = "SELECT image_id,name FROM images;";
	sqlite3_stmt* sql_stmt;
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);
	while (sqlite3_step(sql_stmt) == SQLITE_ROW)
	{
		IDs.push_back(sqlite3_column_int64(sql_stmt, 0));
		names.push_back(reinterpret_cast<const char*>(sqlite3_column_text(sql_stmt, 1)));
	}

	sqlite3_finalize(sql_stmt);
	return 1;
}

bool Db::ReadAllImageIDNameGroup(vector<unsigned>& IDs, vector<string>& names, vector<unsigned>& group_ids)
{
	IDs.clear();
	names.clear();
	group_ids.clear();

	string sql = "SELECT image_id,name,group_id FROM images;";
	sqlite3_stmt* sql_stmt;
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);
	while (sqlite3_step(sql_stmt) == SQLITE_ROW)
	{
		IDs.push_back(sqlite3_column_int64(sql_stmt, 0));
		names.push_back(reinterpret_cast<const char*>(sqlite3_column_text(sql_stmt, 1)));
		group_ids.push_back(sqlite3_column_int64(sql_stmt, 2));
	}

	sqlite3_finalize(sql_stmt);
	return 1;
}

bool Db::ReadAllScaledImageID(vector<unsigned>& IDs)
{
	IDs.clear();

	string sql = "SELECT image_id FROM images_scaled;";
	sqlite3_stmt* sql_stmt;
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);
	while (sqlite3_step(sql_stmt) == SQLITE_ROW)
		IDs.push_back(sqlite3_column_int64(sql_stmt, 0));
	
	sqlite3_finalize(sql_stmt);
	return 1;
}

string Db::ReadImageName(unsigned image_id)
{
	sqlite3_bind_int64(sql_stmt_ReadImageName_, 1, image_id);
	const int result_code = sqlite3_step(sql_stmt_ReadImageName_);

	if (result_code != SQLITE_ROW)
	{
		sqlite3_reset(sql_stmt_ReadImageName_);
		return "NULL";
	}

	string name;

	if (sqlite3_column_type(sql_stmt_ReadImageName_, 0) != SQLITE_NULL)
		name = reinterpret_cast<const char*>(sqlite3_column_text(sql_stmt_ReadImageName_, 0));
	else
		name = "NULL";
	
	sqlite3_reset(sql_stmt_ReadImageName_);
	return name;
}

int Db::ReadImageID(string name)
{
	sqlite3_bind_text(sql_stmt_ReadImageID_, 1, name.c_str(), static_cast<int>(name.size()), SQLITE_STATIC);
	const int result_code = sqlite3_step(sql_stmt_ReadImageID_);
	int ID = -1;

	if (result_code == SQLITE_ROW)
	{
		if (sqlite3_column_type(sql_stmt_ReadImageID_, 0) != SQLITE_NULL)
			ID = sqlite3_column_int64(sql_stmt_ReadImageID_, 0);
	}

	sqlite3_reset(sql_stmt_ReadImageID_);
	return ID;
}

bool Db::ExistKeypoints(int image_id)
{
	int result_code;

	sqlite3_bind_int64(sql_stmt_ExistKeypoints_, 1, image_id);
	result_code = sqlite3_step(sql_stmt_ExistKeypoints_);
	sqlite3_reset(sql_stmt_ExistKeypoints_);

	if (result_code != SQLITE_ROW)
		return 0;
	else
		return 1;
}

bool Db::WriteKeypoints(int image_id, cv::Mat& keys_data, cv::Mat& descs_data)
{
	uLong keysSrcLen = CV_ELEM_SIZE(CV_32F) * keys_data.rows * keys_data.cols;
	uLong keysComLen = compressBound(keysSrcLen);
	Byte* keysCompress = (Byte*)malloc(sizeof(Byte) * keysComLen);
	uLong descSrcLen = CV_ELEM_SIZE(descs_data.type()) * descs_data.rows * descs_data.cols;
	uLong descComLen = compressBound(descSrcLen);
	Byte* descCompress = (Byte*)malloc(sizeof(Byte) * descComLen);

	if (OPT::bFeatureCompress)
	{
		if (compress(keysCompress, &keysComLen, (const Byte *)(keys_data.data), keysSrcLen) != Z_OK ||
			compress(descCompress, &descComLen, (const Byte *)(descs_data.data), descSrcLen) != Z_OK)
		{
			LOG(ERROR) << "Compress data error for image " << image_id;
			return 0;
		}
		LOG(INFO) << fixed << setprecision(1) << "Compress for image " << image_id << ": " << (float)keysComLen / (float)keysSrcLen * 100.f << "%/" << (float)descComLen / (float)descSrcLen * 100.f << "%";
	}

	if (ExistKeypoints(image_id))
	{
		sqlite3_bind_int64(sql_stmt_UpdateKeypoints_, 1, keys_data.rows);
		if (OPT::bFeatureCompress)
		{
			sqlite3_bind_blob(sql_stmt_UpdateKeypoints_, 2, keysCompress, keysComLen, SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_UpdateKeypoints_, 3, descCompress, descComLen, SQLITE_STATIC);
		}
		else
		{
			sqlite3_bind_blob(sql_stmt_UpdateKeypoints_, 2, reinterpret_cast<const char *>(keys_data.data), static_cast<int>(CV_ELEM_SIZE(CV_32F) * keys_data.rows * keys_data.cols), SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_UpdateKeypoints_, 3, reinterpret_cast<const char *>(descs_data.data), static_cast<int>(CV_ELEM_SIZE(descs_data.type()) * descs_data.rows * descs_data.cols), SQLITE_STATIC);
		}
		sqlite3_bind_int64(sql_stmt_UpdateKeypoints_, 4, image_id);
		const int result_code = sqlite3_step(sql_stmt_UpdateKeypoints_);
		sqlite3_reset(sql_stmt_UpdateKeypoints_);
		std::free(keysCompress);
		std::free(descCompress);
		switch (result_code)
		{
		case SQLITE_OK:
		case SQLITE_ROW:
		case SQLITE_DONE:
			return 1;
		default:
			LOG(ERROR) << "SQLite error: " << sqlite3_errstr(result_code);
			return 0;
		}
	}
	else
	{
		sqlite3_bind_int64(sql_stmt_WriteKeypoints_, 1, image_id);
		sqlite3_bind_int64(sql_stmt_WriteKeypoints_, 2, keys_data.rows);
		if (OPT::bFeatureCompress)
		{
			sqlite3_bind_blob(sql_stmt_WriteKeypoints_, 3, keysCompress, keysComLen, SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_WriteKeypoints_, 4, descCompress, descComLen, SQLITE_STATIC);
		}
		else
		{
			sqlite3_bind_blob(sql_stmt_WriteKeypoints_, 3, reinterpret_cast<const char *>(keys_data.data), static_cast<int>(CV_ELEM_SIZE(CV_32F) * keys_data.rows * keys_data.cols), SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_WriteKeypoints_, 4, reinterpret_cast<const char *>(descs_data.data), static_cast<int>(CV_ELEM_SIZE(descs_data.type()) * descs_data.rows * descs_data.cols), SQLITE_STATIC);
		}
		const int result_code = sqlite3_step(sql_stmt_WriteKeypoints_);
		sqlite3_reset(sql_stmt_WriteKeypoints_);
		std::free(keysCompress);
		std::free(descCompress);
		switch (result_code)
		{
		case SQLITE_OK:
		case SQLITE_ROW:
		case SQLITE_DONE:
			return 1;
		default:
			LOG(ERROR) << "SQLite error: " << sqlite3_errstr(result_code);
			return 0;
		}
	}
}

bool Db::WriteImageInfo(int image_id, int valid, int width, int height, float fx, float fy, float u, float v,
						double gps_lon, double gps_lat, double gps_alt, double gps_x, double gps_y, double gps_z)
{	
	if (valid != -1)
		sqlite3_bind_int64(sql_stmt_UpdateImageParam_, 1, valid);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 1);
	
	if (width != -1)
		sqlite3_bind_int64(sql_stmt_UpdateImageParam_, 2, width);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 2);
	
	if (height != -1)
		sqlite3_bind_int64(sql_stmt_UpdateImageParam_, 3, height);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 3);
	
	if (fx != -1)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 4, fx);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 4);
	
	if (fy != -1)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 5, fy);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 5);
	
	if (u != -1)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 6, u);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 6);
	
	if (v != -1)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 7, v);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 7);
	
	if (gps_lon != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 8, gps_lon);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 8);

	if (gps_lat != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 9, gps_lat);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 9);

	if (gps_alt != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 10, gps_alt);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 10);

	if (gps_x != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 11, gps_x);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 11);

	if (gps_y != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 12, gps_y);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 12);

	if (gps_z != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageParam_, 13, gps_z);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageParam_, 13);

	sqlite3_bind_int64(sql_stmt_UpdateImageParam_, 14, image_id);

	SQLITE3_CALL(sql_stmt_UpdateImageParam_);
}

bool Db::ReadKeypoints(int image_id, cv::Mat& keys, cv::Mat& descriptors)
{
	sqlite3_bind_int64(sql_stmt_ReadKeypoints_, 1, image_id);
	const int result_code = sqlite3_step(sql_stmt_ReadKeypoints_);

	if (result_code != SQLITE_ROW)
	{
		sqlite3_reset(sql_stmt_ReadKeypoints_);
		return 0;
	}

	int keysNum = sqlite3_column_int64(sql_stmt_ReadKeypoints_, 0);
	if (OPT::bFeatureCompress)
	{
		int keysComLen = sqlite3_column_bytes(sql_stmt_ReadKeypoints_, 1);
		Byte *keysCompress = (Byte *)malloc(sizeof(Byte) * keysComLen);
		std::memcpy(keysCompress, (const Byte *)sqlite3_column_blob(sql_stmt_ReadKeypoints_, 1), keysComLen);

		int descComLen = sqlite3_column_bytes(sql_stmt_ReadKeypoints_, 2);
		Byte *descCompress = (Byte *)malloc(sizeof(Byte) * descComLen);
		std::memcpy(descCompress, (const Byte *)sqlite3_column_blob(sql_stmt_ReadKeypoints_, 2), descComLen);

		keys = cv::Mat(keysNum, 8, CV_32F);
		if (OPT::bFloatDesc)
			descriptors = cv::Mat(keysNum, OPT::descLength, CV_32F);
		else
			descriptors = cv::Mat(keysNum, OPT::descLength, CV_8U);
		
		uLong keysSrcLen = CV_ELEM_SIZE(CV_32F) * keysNum * 8;
		uLong descSrcLen = CV_ELEM_SIZE(descriptors.type()) * keysNum * OPT::descLength;

		if (uncompress((Byte *)(keys.data), &keysSrcLen, keysCompress, keysComLen) != Z_OK ||
			uncompress((Byte *)(descriptors.data), &descSrcLen, descCompress, descComLen) != Z_OK)
		{
			LOG(ERROR) << "Uncompress data error for image " << image_id;
			std::free(keysCompress);
			std::free(descCompress);
			sqlite3_reset(sql_stmt_ReadKeypoints_);
			return 0;
		}
		std::free(keysCompress);
		std::free(descCompress);
	}
	else
	{
		int length = CV_ELEM_SIZE(CV_32F) * keysNum * 8;
		keys = cv::Mat(keysNum, 8, CV_32F);
		std::memcpy(reinterpret_cast<char *>(keys.data), sqlite3_column_blob(sql_stmt_ReadKeypoints_, 1), length);		
		if (OPT::bFloatDesc)
			descriptors = cv::Mat(keysNum, OPT::descLength, CV_32F);
		else
			descriptors = cv::Mat(keysNum, OPT::descLength, CV_8U);
		length = CV_ELEM_SIZE(descriptors.type()) * keysNum * OPT::descLength;
		std::memcpy(reinterpret_cast<char *>(descriptors.data), sqlite3_column_blob(sql_stmt_ReadKeypoints_, 2), length);
	}

	sqlite3_reset(sql_stmt_ReadKeypoints_);
	return 1;
}

int Db::ReadImageGPS(int image_id, double& lon, double& lat, double& alt, double& gps_x, double& gps_y, double& gps_z)
{
	sqlite3_bind_int64(sql_stmt_ReadImageGPS_, 1, image_id);
	const int result_code = sqlite3_step(sql_stmt_ReadImageGPS_);

	lon = DBL_MAX;
	lat = DBL_MAX;
	alt = DBL_MAX;
	gps_x = DBL_MAX;
	gps_y = DBL_MAX;
	gps_z = DBL_MAX;
	int flag = 0;

	if (result_code != SQLITE_ROW)
	{
		sqlite3_reset(sql_stmt_ReadImageGPS_);
		return 0;
	}

	if (sqlite3_column_type(sql_stmt_ReadImageGPS_, 0) == SQLITE_FLOAT && sqlite3_column_type(sql_stmt_ReadImageGPS_, 1) == SQLITE_FLOAT && sqlite3_column_type(sql_stmt_ReadImageGPS_, 2) == SQLITE_FLOAT)
	{
		lon = sqlite3_column_double(sql_stmt_ReadImageGPS_, 0);
		lat = sqlite3_column_double(sql_stmt_ReadImageGPS_, 1);
		alt = sqlite3_column_double(sql_stmt_ReadImageGPS_, 2);
		flag = 2;
	}

	if (sqlite3_column_type(sql_stmt_ReadImageGPS_, 3) == SQLITE_FLOAT && sqlite3_column_type(sql_stmt_ReadImageGPS_, 4) == SQLITE_FLOAT && sqlite3_column_type(sql_stmt_ReadImageGPS_, 5) == SQLITE_FLOAT)
	{
		gps_x = sqlite3_column_double(sql_stmt_ReadImageGPS_, 3);
		gps_y = sqlite3_column_double(sql_stmt_ReadImageGPS_, 4);
		gps_z = sqlite3_column_double(sql_stmt_ReadImageGPS_, 5);
		flag = 1;
	}

	sqlite3_reset(sql_stmt_ReadImageGPS_);
	return flag;
}

bool Db::UpdateImageGPS(int image_id, double lon, double lat, double alt, double gps_x, double gps_y, double gps_z)
{
	if (lon != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageGPS_, 1, lon);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageGPS_, 1);
	
	if (lat != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageGPS_, 2, lat);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageGPS_, 2);

	if (alt != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageGPS_, 3, alt);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageGPS_, 3);

	if (gps_x != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageGPS_, 4, gps_x);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageGPS_, 4);

	if (gps_y != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageGPS_, 5, gps_y);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageGPS_, 5);

	if (gps_z != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImageGPS_, 6, gps_z);
	else
		sqlite3_bind_null(sql_stmt_UpdateImageGPS_, 6);

	sqlite3_bind_int64(sql_stmt_UpdateImageGPS_, 7, image_id);

	SQLITE3_CALL(sql_stmt_UpdateImageGPS_);
}

bool Db::UpdateImagePose(int image_id, double cx, double cy, double cz, cv::Matx33f& R)
{
	if (cx != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImagePose_, 1, cx);
	else
		sqlite3_bind_null(sql_stmt_UpdateImagePose_, 1);

	if (cy != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImagePose_, 2, cy);
	else
		sqlite3_bind_null(sql_stmt_UpdateImagePose_, 2);

	if (cz != DBL_MAX)
		sqlite3_bind_double(sql_stmt_UpdateImagePose_, 3, cz);
	else
		sqlite3_bind_null(sql_stmt_UpdateImagePose_, 3);
	
	if (cv::countNonZero(R) != 0)
		sqlite3_bind_blob(sql_stmt_UpdateImagePose_, 4, reinterpret_cast<const char*>(R.val), static_cast<int>(sizeof(float) * 9), SQLITE_STATIC);
	else
		sqlite3_bind_null(sql_stmt_UpdateImagePose_, 4);

	sqlite3_bind_int64(sql_stmt_UpdateImagePose_, 5, image_id);

	SQLITE3_CALL(sql_stmt_UpdateImagePose_);
}

bool Db::UpdateInitCams(int group_id, float fx, float fy, float u, float v, float k1, float k2, float k3, float k4, float k5)
{
	if (fx != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 1, fx);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 1);
	
	if (fy != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 2, fy);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 2);
	
	if (u != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 3, u);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 3);
	
	if (v != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 4, v);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 4);

	if (k1 != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 5, k1);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 5);

	if (k2 != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 6, k2);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 6);

	if (k3 != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 7, k3);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 7);

	if (k4 != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 8, k4);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 8);

	if (k5 != -1)
		sqlite3_bind_double(sql_stmt_UpdateInitCams_, 9, k5);
	else
		sqlite3_bind_null(sql_stmt_UpdateInitCams_, 9);

	sqlite3_bind_int64(sql_stmt_UpdateInitCams_, 10, group_id);

	SQLITE3_CALL(sql_stmt_UpdateInitCams_);
}

bool Db::ResetInitCams(int image_id, float fx, float fy, float u, float v, float k1, float k2, float k3, float k4, float k5)
{
	if (fx != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 1, fx);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 1);
	
	if (fy != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 2, fy);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 2);
	
	if (u != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 3, u);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 3);
	
	if (v != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 4, v);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 4);

	if (k1 != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 5, k1);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 5);

	if (k2 != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 6, k2);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 6);

	if (k3 != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 7, k3);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 7);

	if (k4 != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 8, k4);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 8);

	if (k5 != -1)
		sqlite3_bind_double(sql_stmt_ResetInitCams_, 9, k5);
	else
		sqlite3_bind_null(sql_stmt_ResetInitCams_, 9);

	sqlite3_bind_int64(sql_stmt_ResetInitCams_, 10, image_id);

	SQLITE3_CALL(sql_stmt_ResetInitCams_);
}

bool Db::CleanMatches()
{
	SQLITE3_EXEC(database_, "DELETE FROM matches;");
	return 1;
}

bool Db::ExistMatches(int image_id)
{
	int result_code;

	sqlite3_bind_int64(sql_stmt_ExistMatches_, 1, image_id);
	result_code = sqlite3_step(sql_stmt_ExistMatches_);
	sqlite3_reset(sql_stmt_ExistMatches_);

	if (result_code != SQLITE_ROW)
		return 0;
	else
		return 1;
}

bool Db::WriteMatches(int image_id, vector<int>& match_data, vector<double>& model)
{
	if (match_data.empty())
		return 0;

	int matchesNum = match_data.size();
	int modelNum = model.size();

	if (ExistMatches(image_id))
	{
		sqlite3_bind_int64(sql_stmt_UpdateMatches_, 1, matchesNum);
		sqlite3_bind_blob(sql_stmt_UpdateMatches_, 2, reinterpret_cast<const char*>(match_data.data()), static_cast<int>(sizeof(int) * matchesNum), SQLITE_STATIC);
		sqlite3_bind_int64(sql_stmt_UpdateMatches_, 3, modelNum);
		sqlite3_bind_blob(sql_stmt_UpdateMatches_, 4, reinterpret_cast<const char*>(model.data()), static_cast<int>(sizeof(double) * modelNum), SQLITE_STATIC);
		sqlite3_bind_int64(sql_stmt_UpdateMatches_, 5, image_id);
		SQLITE3_CALL(sql_stmt_UpdateMatches_);
	}
	else
	{
		sqlite3_bind_int64(sql_stmt_WriteMatches_, 1, image_id);
		sqlite3_bind_int64(sql_stmt_WriteMatches_, 2, matchesNum);
		sqlite3_bind_blob(sql_stmt_WriteMatches_, 3, reinterpret_cast<const char*>(match_data.data()), static_cast<int>(sizeof(int) * matchesNum), SQLITE_STATIC);
		sqlite3_bind_int64(sql_stmt_WriteMatches_, 4, modelNum);
		sqlite3_bind_blob(sql_stmt_WriteMatches_, 5, reinterpret_cast<const char*>(model.data()), static_cast<int>(sizeof(double) * modelNum), SQLITE_STATIC);
		SQLITE3_CALL(sql_stmt_WriteMatches_);
	}
}

bool Db::ResetInitCamsFromTxt(string camsTxtFile)
{
	if (!boost::filesystem::exists(camsTxtFile))
	{
		LOG(ERROR) << "Fail to load initcams file: " << camsTxtFile;
		return 0;
	}

	BeginTransaction();

	ifstream in(camsTxtFile);
	while (!in.eof())
	{
		int groupId = -1;
		string token;
		in >> groupId;

		if (groupId < 0 || !ExistGroupID(groupId))
		{
			getline(in, token);
			continue;
		}

		float fx = -1, fy = -1, u = -1, v = -1, k1 = -1, k2 = -1;
		in >> fx >> fy >> u >> v >> k1 >> k2;
		getline(in, token);

		if (UpdateInitCams(groupId, fx, fy, u, v, k1, k2))
		{
			LOG(INFO) << "Write initial camera parameters for group " << groupId << " into DB (" << fixed << setprecision(2) << fx << ", " << fy
					  << ", " << u << ", " << v << ", " << k1 << ", " << k2 << ")";
		}
		else
			LOG(ERROR) << "Failed to write initial camera parameters for group " << groupId << " into DB";
	}

	in.close();
	CommitTransaction();
	return 1;
}

bool Db::ResetInitCamsFromBin(string camsBinFile)
{
	ImageArr images;
	if (!CameraBinLoad(camsBinFile, images))
	{
		LOG(INFO) << "Fail to load initcams file: : " << camsBinFile;
		return 0;
	}

	int imageNum = images.size();
	int currentNum = 1;
	BeginTransaction();

	#pragma omp parallel for schedule(dynamic)
	for (int i = 0; i < imageNum; i++)
	{
		Image &image = images[i];
		#pragma omp critical
		{
			if (ResetInitCams(image.ID, image.K(0, 0), image.K(1, 1), image.K(0, 2), image.K(1, 2)))
			{
				// LOG(INFO) << "Reset initial camera parameters for image " << image.ID << " into DB (" << fixed << setprecision(2)
				// 		  << image.K(0, 0) << ", " << image.K(1, 1) << ", " << image.K(0, 2) << ", " << image.K(1, 2) << "), "
				// 		  << currentNum << "/" << imageNum;
			}
			else
				LOG(ERROR) << "Failed to reset initial camera parameters for image " << image.ID << " into DB";
			currentNum++;
		}
	}

	CommitTransaction();
	return 1;
}

bool Db::ReadAllMatches(ImageInfos& imageInfos, ImagePairMatches& imagePairMatches, bool initPoses)
{
	int result_code;
	sqlite3_stmt* sql_stmt;
	string sql;

	sql = "SELECT COUNT(*) FROM images;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);
	result_code = sqlite3_step(sql_stmt);

	int imageNum = 0;
	if (result_code == SQLITE_ROW)
		imageNum = sqlite3_column_int64(sql_stmt, 0);
	else
	{
		sqlite3_finalize(sql_stmt);
		return 0;
	}

	if (imageNum == 0)
	{
		LOG(ERROR) << "No images in DB";
		sqlite3_finalize(sql_stmt);
		return 0;
	}
	else
		LOG(INFO) << "Number of images in DB: " << imageNum;
	
	imageInfos.resize(imageNum);

	sql = "SELECT image_id,group_id,width,height,fx,fy,u,v,k1,k2,gps_x,gps_y,gps_z,cx,cy,cz,R FROM images;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);
	for (int i = 0; i < imageNum; i++)
	{
		if (sqlite3_step(sql_stmt) != SQLITE_ROW)
		{
			LOG(ERROR) << "Failed to read image info " << i << " from DB";
			sqlite3_finalize(sql_stmt);
			return 0;
		}
		ImageInfo& imageInfo = imageInfos[i];
		imageInfo.ID = sqlite3_column_int64(sql_stmt, 0);
		imageInfo.group_id = sqlite3_column_int64(sql_stmt, 1);
		imageInfo.width = sqlite3_column_int64(sql_stmt, 2);
		imageInfo.height = sqlite3_column_int64(sql_stmt, 3);
		imageInfo.fx = sqlite3_column_double(sql_stmt, 4);
		imageInfo.fy = sqlite3_column_double(sql_stmt, 5);
		imageInfo.u = sqlite3_column_double(sql_stmt, 6);
		imageInfo.v = sqlite3_column_double(sql_stmt, 7);
		imageInfo.k1 = sqlite3_column_double(sql_stmt, 8);
		imageInfo.k2 = sqlite3_column_double(sql_stmt, 9);
		// LOG(INFO) << "ID:" << imageInfo.ID << ", group ID:" << imageInfo.group_id << ", width=" << imageInfo.width << ", height=" << imageInfo.height 
		// 	<< ", fx=" << imageInfo.fx << ", fy=" << imageInfo.fy << ", u=" << imageInfo.u << ", v=" << imageInfo.v << ", k1=" << imageInfo.k1 << ", k2=" << imageInfo.k2;
		if(sqlite3_column_type(sql_stmt, 10) == SQLITE_FLOAT && sqlite3_column_type(sql_stmt, 11) == SQLITE_FLOAT && sqlite3_column_type(sql_stmt, 12) == SQLITE_FLOAT)
		{
			imageInfo.gps_x = sqlite3_column_double(sql_stmt, 10);
			imageInfo.gps_y = sqlite3_column_double(sql_stmt, 11);
			imageInfo.gps_z = sqlite3_column_double(sql_stmt, 12);
		}
		if(initPoses && sqlite3_column_type(sql_stmt, 13) == SQLITE_FLOAT && sqlite3_column_type(sql_stmt, 14) == SQLITE_FLOAT && sqlite3_column_type(sql_stmt, 15) == SQLITE_FLOAT)
		{
			imageInfo.cx = sqlite3_column_double(sql_stmt, 13);
			imageInfo.cy = sqlite3_column_double(sql_stmt, 14);
			imageInfo.cz = sqlite3_column_double(sql_stmt, 15);
		}
		if (initPoses && sqlite3_column_type(sql_stmt, 16) != SQLITE_NULL)
		{
			imageInfo.R.resize(9);
			std::memcpy(reinterpret_cast<char *>(imageInfo.R.data()), sqlite3_column_blob(sql_stmt, 16), sizeof(float) * 9);
		}
	}
	LOG(INFO) << "Load parameters for " << imageNum << " images";

	map<int, int> indexID;
	for (int i = 0; i < imageInfos.size(); i++)
	{
		indexID[imageInfos[i].ID] = i;
	}

	int num = 0;
	sql = "SELECT image_id,keys_num,keys_data FROM keypoints;";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);
	while (sqlite3_step(sql_stmt) == SQLITE_ROW)
	{
		int ID = sqlite3_column_int64(sql_stmt, 0);
		int imageIdx = indexID[ID];
		ImageInfo& imageInfo = imageInfos[imageIdx];
		int keysNum = sqlite3_column_int64(sql_stmt, 1);
		imageInfo.keys.resize(keysNum);

		if (OPT::bFeatureCompress)
		{
			int keysComLen = sqlite3_column_bytes(sql_stmt, 2);
			Byte *keysCompress = (Byte *)malloc(sizeof(Byte) * keysComLen);
			std::memcpy(keysCompress, (const Byte *)sqlite3_column_blob(sql_stmt, 2), keysComLen);

			cv::Mat keys = cv::Mat(keysNum, 8, CV_32F);
			uLong keysSrcLen = CV_ELEM_SIZE(CV_32F) * keysNum * 8;

			if (uncompress((Byte *)(keys.data), &keysSrcLen, keysCompress, keysComLen) != Z_OK)
			{
				LOG(ERROR) << "Uncompress data error for image " << ID;
				std::free(keysCompress);
				sqlite3_reset(sql_stmt);
				return 0;
			}			
			std::free(keysCompress);
			float *data = (float *)keys.data;
			for (int j = 0; j < keysNum; j++)
			{
				imageInfo.keys[j].x = *(data++);
				imageInfo.keys[j].y = *(data++);
				data += 3;
				imageInfo.keys[j].r = *(data++);
				imageInfo.keys[j].g = *(data++);
				imageInfo.keys[j].b = *(data++);
			}
		}
		else
		{
			float *data = (float *)(sqlite3_column_blob(sql_stmt, 2));
			for (int j = 0; j < keysNum; j++)
			{
				imageInfo.keys[j].x = *(data++);
				imageInfo.keys[j].y = *(data++);
				data += 3;
				imageInfo.keys[j].r = *(data++);
				imageInfo.keys[j].g = *(data++);
				imageInfo.keys[j].b = *(data++);
			}
		}
		num++;
		//LOG(INFO) << "Load " << keysNum << " keypoints for image " << ID;
	}
	LOG(INFO) << "Load keypoints for " << num << " images";

	num = 0;
	sql = "SELECT image_id,match_data_num,match_data,model_data FROM matches";
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);
	while (sqlite3_step(sql_stmt) == SQLITE_ROW)
	{
		int image_id1 = sqlite3_column_int64(sql_stmt, 0);
		if(sqlite3_column_type(sql_stmt, 1) == SQLITE_NULL || sqlite3_column_type(sql_stmt, 2) == SQLITE_NULL)
		{
			LOG(INFO) << "Match NULL for image " << image_id1;
			continue;
		}
		int match_data_num = sqlite3_column_int64(sql_stmt, 1);
		int* data = (int*)(sqlite3_column_blob(sql_stmt, 2));
		double* model_data = (double*)(sqlite3_column_blob(sql_stmt, 3));
		int j = 0;
		bool flag = 0;
		while (j < match_data_num - 1)
		{
			ImagePairMatch imagePairMatch;
			imagePairMatch.image_id1 = image_id1;
			imagePairMatch.image_id2 = *(data++);
			if (imagePairMatch.image_id1 > imagePairMatch.image_id2)
			{
				flag = 1;
				swap(imagePairMatch.image_id1, imagePairMatch.image_id2);
			}
			imagePairMatch.model = *(data++);
			int match_num = *(data++);			
			j = j + 3;
			if (match_num == 0)
				continue;
			imagePairMatch.keyMatches.resize(match_num);
			for (int k = 0; k < match_num; k++)
			{
				if (flag)
				{
					imagePairMatch.keyMatches[k].keyIdx2 = *(data++);
					imagePairMatch.keyMatches[k].keyIdx1 = *(data++);
				}
				else
				{
					imagePairMatch.keyMatches[k].keyIdx1 = *(data++);
					imagePairMatch.keyMatches[k].keyIdx2 = *(data++);
				}
				j = j + 2;
			}
			for (int k = 0; k < 9; k++)
				imagePairMatch.M.push_back(*(model_data++));
			imagePairMatches.push_back(imagePairMatch);
		}
		num++;
	}
	LOG(INFO) << "Load matches for " << num << " images";

	sqlite3_finalize(sql_stmt);
	return 1;
}

bool Db::WriteSfMResults(int image_id, int valid, float fx, float fy, float u, float v, float k1, float k2, float k3, float k4, float k5, 
						 double cx, double cy, double cz, cv::Matx33f& R)
{
	sqlite3_bind_int64(sql_stmt_WriteSfMResult_, 1, valid);
	
	if (fx != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 2, fx);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 2);
	
	if (fy != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 3, fy);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 3);
	
	if (u != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 4, u);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 4);
	
	if (v != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 5, v);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 5);

	if (k1 != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 6, k1);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 6);

	if (k2 != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 7, k2);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 7);

	if (k3 != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 8, k3);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 8);

	if (k4 != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 9, k4);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 9);

	if (k5 != -1)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 10, k5);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 10);
	
	if (cx != DBL_MAX)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 11, cx);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 11);

	if (cy != DBL_MAX)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 12, cy);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 12);

	if (cz != DBL_MAX)
		sqlite3_bind_double(sql_stmt_WriteSfMResult_, 13, cz);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 13);
	
	if (cv::countNonZero(R) != 0)
		sqlite3_bind_blob(sql_stmt_WriteSfMResult_, 14, reinterpret_cast<const char*>(R.val), static_cast<int>(sizeof(float) * 9), SQLITE_STATIC);
	else
		sqlite3_bind_null(sql_stmt_WriteSfMResult_, 14);

	sqlite3_bind_int64(sql_stmt_WriteSfMResult_, 15, image_id);

	SQLITE3_CALL(sql_stmt_WriteSfMResult_);
}

bool Db::CleanImageScaled()
{
	SQLITE3_EXEC(database_, "DELETE FROM images_scaled;");
	return 1;
}

bool Db::ReadOriginalImageInfo(Image& image)
{
	sqlite3_bind_int64(sql_stmt_ReadOriginalImageInfo_, 1, image.ID);
	const int result_code = sqlite3_step(sql_stmt_ReadOriginalImageInfo_);

	if (result_code != SQLITE_ROW)
	{
		sqlite3_reset(sql_stmt_ReadOriginalImageInfo_);
		return 0;
	}
	
	image.name = reinterpret_cast<const char *>(sqlite3_column_text(sql_stmt_ReadOriginalImageInfo_, 0));
	image.group = sqlite3_column_int64(sql_stmt_ReadOriginalImageInfo_, 1);
	image.valid = sqlite3_column_int64(sql_stmt_ReadOriginalImageInfo_, 2);
	image.width = sqlite3_column_int64(sql_stmt_ReadOriginalImageInfo_, 3);
	image.height = sqlite3_column_int64(sql_stmt_ReadOriginalImageInfo_, 4);
	image.K = cv::Matx33f::zeros();
	image.K(2, 2) = 1;
	image.K(0, 0) = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 5);
	image.K(1, 1) = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 6);
	image.K(0, 2) = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 7);
	image.K(1, 2) = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 8);
	image.k1 = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 9);
	image.k2 = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 10);
	image.k3 = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 11);
	image.k4 = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 12);
	image.k5 = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 13);
	image.C.x = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 14);
	image.C.y = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 15);
	image.C.z = sqlite3_column_double(sql_stmt_ReadOriginalImageInfo_, 16);
	if (sqlite3_column_type(sql_stmt_ReadOriginalImageInfo_, 17) != SQLITE_NULL)
	{
		std::memcpy(reinterpret_cast<char *>(image.R.val), sqlite3_column_blob(sql_stmt_ReadOriginalImageInfo_, 17), sizeof(float) * 9);
		image.T = -image.R * image.C;
		hconcat(image.K * image.R, cv::Mat(image.K * image.T), image.P);
	}
	sqlite3_reset(sql_stmt_ReadOriginalImageInfo_);
	return 1;
}

bool Db::ExistImageScaled(int image_id)
{
	int result_code;

	sqlite3_bind_int64(sql_stmt_ExistImageScaled_, 1, image_id);
	result_code = sqlite3_step(sql_stmt_ExistImageScaled_);
	sqlite3_reset(sql_stmt_ExistImageScaled_);

	if (result_code != SQLITE_ROW)
		return 0;
	else
		return 1;
}

bool Db::ReadScaledImageInfo(Image& image, bool with_nrighbors, bool with_points)
{
	sqlite3_bind_int64(sql_stmt_ReadImageScaledParam_, 1, image.ID);
	const int result_code = sqlite3_step(sql_stmt_ReadImageScaledParam_);

	if (result_code != SQLITE_ROW)
	{
		sqlite3_reset(sql_stmt_ReadImageScaledParam_);
		return 0;
	}

	image.name = reinterpret_cast<const char *>(sqlite3_column_text(sql_stmt_ReadImageScaledParam_, 0));
	image.valid = sqlite3_column_int64(sql_stmt_ReadImageScaledParam_, 1);
	image.scale = sqlite3_column_int64(sql_stmt_ReadImageScaledParam_, 2);
	image.width = sqlite3_column_int64(sql_stmt_ReadImageScaledParam_, 3);
	image.height = sqlite3_column_int64(sql_stmt_ReadImageScaledParam_, 4);
	image.K = cv::Matx33f::zeros();
	image.K(2, 2) = 1;
	image.K(0, 0) = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 5);
	image.K(1, 1) = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 6);
	image.K(0, 2) = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 7);
	image.K(1, 2) = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 8);
	image.C.x = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 9);
	image.C.y = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 10);
	image.C.z = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 11);
	if (sqlite3_column_type(sql_stmt_ReadImageScaledParam_, 12) != SQLITE_NULL)
	{
		std::memcpy(reinterpret_cast<char *>(image.R.val), sqlite3_column_blob(sql_stmt_ReadImageScaledParam_, 12), sizeof(float) * 9);
		image.T = -image.R * image.C;
		hconcat(image.K * image.R, cv::Mat(image.K * image.T), image.P);
	}

	if (with_nrighbors)
	{
		image.dMin = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 13);
		image.dMax = sqlite3_column_double(sql_stmt_ReadImageScaledParam_, 14);

		int neighborNum = sqlite3_column_int64(sql_stmt_ReadImageScaledParam_, 15);
		if (neighborNum > 0)
		{
			cv::Mat neighborData(neighborNum * 8, 3, CV_32F);
			std::memcpy(reinterpret_cast<char *>(neighborData.data), sqlite3_column_blob(sql_stmt_ReadImageScaledParam_, 16), sizeof(float) * neighborNum * 24);

			image.neighbors.resize(neighborNum);
			float *value = reinterpret_cast<float *>(neighborData.data);

			for (NeighborInfo &neighbor : image.neighbors)
			{
				neighbor.ID = *(value++);
				value += 2;
				neighbor.Hl(0, 0) = *(value++);
				neighbor.Hl(0, 1) = *(value++);
				neighbor.Hl(0, 2) = *(value++);
				neighbor.Hl(1, 0) = *(value++);
				neighbor.Hl(1, 1) = *(value++);
				neighbor.Hl(1, 2) = *(value++);
				neighbor.Hl(2, 0) = *(value++);
				neighbor.Hl(2, 1) = *(value++);
				neighbor.Hl(2, 2) = *(value++);
				neighbor.Hm.x = *(value++);
				neighbor.Hm.y = *(value++);
				neighbor.Hm.z = *(value++);
				neighbor.Hr(0, 0) = *(value++);
				neighbor.Hr(0, 1) = *(value++);
				neighbor.Hr(0, 2) = *(value++);
				neighbor.Hr(1, 0) = *(value++);
				neighbor.Hr(1, 1) = *(value++);
				neighbor.Hr(1, 2) = *(value++);
				neighbor.Hr(2, 0) = *(value++);
				neighbor.Hr(2, 1) = *(value++);
				neighbor.Hr(2, 2) = *(value++);
			}
		}
	}

	if (with_points)
	{
		int pointsNum = sqlite3_column_int64(sql_stmt_ReadImageScaledParam_, 17);
		image.points.resize(pointsNum);
		std::memcpy(reinterpret_cast<char *>(&(image.points[0].x)), sqlite3_column_blob(sql_stmt_ReadImageScaledParam_, 18), sizeof(float) * pointsNum * 3);
	}

	sqlite3_reset(sql_stmt_ReadImageScaledParam_);
	return 1;
}

bool Db::WriteImageScaledInfo(int image_id, string name, int valid, float scale, int width, int height, float fx, float fy, float u, float v,						
						float cx, float cy, float cz, cv::Matx33f& R)
{
	if (ExistImageScaled(image_id))
	{
		sqlite3_bind_text(sql_stmt_UpdateImageScaledParam_, 1, name.c_str(), static_cast<int>(name.size()), SQLITE_STATIC);
		sqlite3_bind_int64(sql_stmt_UpdateImageScaledParam_, 2, valid);
		sqlite3_bind_double(sql_stmt_UpdateImageScaledParam_, 3, scale);
		sqlite3_bind_int64(sql_stmt_UpdateImageScaledParam_, 4, width);
		sqlite3_bind_int64(sql_stmt_UpdateImageScaledParam_, 5, height);
		sqlite3_bind_double(sql_stmt_UpdateImageScaledParam_, 6, fx);
		sqlite3_bind_double(sql_stmt_UpdateImageScaledParam_, 7, fy);
		sqlite3_bind_double(sql_stmt_UpdateImageScaledParam_, 8, u);
		sqlite3_bind_double(sql_stmt_UpdateImageScaledParam_, 9, v);
		sqlite3_bind_double(sql_stmt_UpdateImageScaledParam_, 10, cx);
		sqlite3_bind_double(sql_stmt_UpdateImageScaledParam_, 11, cy);
		sqlite3_bind_double(sql_stmt_UpdateImageScaledParam_, 12, cz);
		sqlite3_bind_blob(sql_stmt_UpdateImageScaledParam_, 13, reinterpret_cast<const char *>(R.val), static_cast<int>(sizeof(float) * 9), SQLITE_STATIC);		
		sqlite3_bind_int64(sql_stmt_UpdateImageScaledParam_, 14, image_id);
		SQLITE3_CALL(sql_stmt_UpdateImageScaledParam_);
	}
	else
	{
		sqlite3_bind_int64(sql_stmt_WriteImageScaledParam_, 1, image_id);
		sqlite3_bind_text(sql_stmt_WriteImageScaledParam_, 2, name.c_str(), static_cast<int>(name.size()), SQLITE_STATIC);
		sqlite3_bind_int64(sql_stmt_WriteImageScaledParam_, 3, valid);
		sqlite3_bind_double(sql_stmt_WriteImageScaledParam_, 4, scale);
		sqlite3_bind_int64(sql_stmt_WriteImageScaledParam_, 5, width);
		sqlite3_bind_int64(sql_stmt_WriteImageScaledParam_, 6, height);
		sqlite3_bind_double(sql_stmt_WriteImageScaledParam_, 7, fx);
		sqlite3_bind_double(sql_stmt_WriteImageScaledParam_, 8, fy);
		sqlite3_bind_double(sql_stmt_WriteImageScaledParam_, 9, u);
		sqlite3_bind_double(sql_stmt_WriteImageScaledParam_, 10, v);
		sqlite3_bind_double(sql_stmt_WriteImageScaledParam_, 11, cx);
		sqlite3_bind_double(sql_stmt_WriteImageScaledParam_, 12, cy);
		sqlite3_bind_double(sql_stmt_WriteImageScaledParam_, 13, cz);
		sqlite3_bind_blob(sql_stmt_WriteImageScaledParam_, 14, reinterpret_cast<const char *>(R.val), static_cast<int>(sizeof(float) * 9), SQLITE_STATIC);
		SQLITE3_CALL(sql_stmt_WriteImageScaledParam_);
	}
	
}
bool Db::WriteNeighbors(Image& image)
{
	if (image.valid)
	{
		int neighborNum = image.neighbors.size();
		cv::Mat neighborData(neighborNum * 8, 3, CV_32F);
		float* value = reinterpret_cast<float*>(neighborData.data);

		for (NeighborInfo neighbor : image.neighbors)
		{
			*(value++) = neighbor.ID;
			*(value++) = 0;
			*(value++) = 0;
			*(value++) = neighbor.Hl(0, 0);
			*(value++) = neighbor.Hl(0, 1);
			*(value++) = neighbor.Hl(0, 2);
			*(value++) = neighbor.Hl(1, 0);
			*(value++) = neighbor.Hl(1, 1);
			*(value++) = neighbor.Hl(1, 2);
			*(value++) = neighbor.Hl(2, 0);
			*(value++) = neighbor.Hl(2, 1);
			*(value++) = neighbor.Hl(2, 2);
			*(value++) = neighbor.Hm.x;
			*(value++) = neighbor.Hm.y;
			*(value++) = neighbor.Hm.z;
			*(value++) = neighbor.Hr(0, 0);
			*(value++) = neighbor.Hr(0, 1);
			*(value++) = neighbor.Hr(0, 2);
			*(value++) = neighbor.Hr(1, 0);
			*(value++) = neighbor.Hr(1, 1);
			*(value++) = neighbor.Hr(1, 2);
			*(value++) = neighbor.Hr(2, 0);
			*(value++) = neighbor.Hr(2, 1);
			*(value++) = neighbor.Hr(2, 2);
		}

		int result_code;

		sqlite3_bind_double(sql_stmt_WriteNeighbors_, 1, image.dMin);
		sqlite3_bind_double(sql_stmt_WriteNeighbors_, 2, image.dMax);
		sqlite3_bind_int64(sql_stmt_WriteNeighbors_, 3, neighborNum);
		sqlite3_bind_blob(sql_stmt_WriteNeighbors_, 4, reinterpret_cast<const char*>(neighborData.data), static_cast<int>(sizeof(float) * neighborNum * 24), SQLITE_STATIC);
		sqlite3_bind_int64(sql_stmt_WriteNeighbors_, 5, image.points.size());
		sqlite3_bind_blob(sql_stmt_WriteNeighbors_, 6, reinterpret_cast<const char*>(&(image.points[0].x)), static_cast<int>(sizeof(float) * image.points.size() * 3), SQLITE_STATIC);
		sqlite3_bind_int64(sql_stmt_WriteNeighbors_, 7, image.ID);
		SQLITE3_CALL(sql_stmt_WriteNeighbors_);
	}
	else
	{
		LOG(INFO) << "Image " << image.ID << " is invalid";
		return 0;
	}
}

bool Db::CleanDepthmaps()
{
	SQLITE3_EXEC(database_, "DELETE FROM depthmaps;");
	return 1;
}

bool Db::ExistDepthMap(int image_id)
{
	int result_code;

	sqlite3_bind_int64(sql_stmt_ExistDepthMap_, 1, image_id);
	result_code = sqlite3_step(sql_stmt_ExistDepthMap_);
	sqlite3_reset(sql_stmt_ExistDepthMap_);

	if (result_code != SQLITE_ROW)
		return 0;
	else
		return 1;
}

bool Db::WriteDepthMap(Image& image)
{
	if (!image.valid || image.depthMap.empty() || image.confMap.empty() || image.confMap.empty())
	{
		LOG(INFO) << "Image " << image.ID << " is invalid";
		return 0;
	}

	uLong depthSrcLen = CV_ELEM_SIZE(CV_32F) * image.width * image.height;
	uLong confSrcLen = depthSrcLen;
	uLong normalSrcLen = CV_ELEM_SIZE(CV_32FC3) * image.width * image.height;
	uLong depthLen = compressBound(depthSrcLen);
	uLong confLen = depthLen;
	uLong normalLen = compressBound(normalSrcLen);
	Byte* depthCompress = (Byte*)malloc(sizeof(Byte) * depthLen);
	Byte* confCompress = (Byte*)malloc(sizeof(Byte) * confLen);
	Byte* normalCompress = (Byte*)malloc(sizeof(Byte) * normalLen);

	if (OPT::bCompress)
	{
		if (compress(depthCompress, &depthLen, (const Byte *)(image.depthMap.data), depthSrcLen) != Z_OK ||
			compress(confCompress, &confLen, (const Byte *)(image.confMap.data), confSrcLen) != Z_OK || 
			compress(normalCompress, &normalLen, (const Byte *)(image.normalMap.data), normalSrcLen) != Z_OK)
		{
			LOG(ERROR) << "Compress data error for image " << image.ID;
			return 0;
		}
		LOG(INFO) << fixed << setprecision(1) << "Compress for image " << image.ID << ": " 
				  << (float)depthLen / (float)depthSrcLen * 100.f << "%, "
				  << (float)confLen / (float)confSrcLen * 100.f << "%, "
				  << (float)normalLen / (float)normalSrcLen * 100.f << "%";
	}

	if (ExistDepthMap(image.ID))
	{
		sqlite3_bind_int64(sql_stmt_UpdateDepthMap_, 1, image.valid);
		sqlite3_bind_int64(sql_stmt_UpdateDepthMap_, 2, image.num);
		if (OPT::bCompress)
		{
			sqlite3_bind_blob(sql_stmt_UpdateDepthMap_, 3, depthCompress, depthLen, SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_UpdateDepthMap_, 4, confCompress, confLen, SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_UpdateDepthMap_, 5, normalCompress, normalLen, SQLITE_STATIC);
		}
		else
		{
			sqlite3_bind_blob(sql_stmt_UpdateDepthMap_, 3, reinterpret_cast<const char *>(image.depthMap.data), static_cast<int>(CV_ELEM_SIZE(CV_32F) * image.width * image.height), SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_UpdateDepthMap_, 4, reinterpret_cast<const char *>(image.confMap.data), static_cast<int>(CV_ELEM_SIZE(CV_32F) * image.width * image.height), SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_UpdateDepthMap_, 5, reinterpret_cast<const char *>(image.normalMap.data), static_cast<int>(CV_ELEM_SIZE(CV_32FC3) * image.width * image.height), SQLITE_STATIC);
		}
		sqlite3_bind_int64(sql_stmt_UpdateDepthMap_, 6, image.ID);
		const int result_code = sqlite3_step(sql_stmt_UpdateDepthMap_);
		sqlite3_reset(sql_stmt_UpdateDepthMap_);
		std::free(depthCompress);
		std::free(confCompress);
		std::free(normalCompress);
		switch (result_code)
		{
		case SQLITE_OK:
		case SQLITE_ROW:
		case SQLITE_DONE:
			return 1;
		default:
			LOG(ERROR) << "SQLite error: " << sqlite3_errstr(result_code);
			return 0;
		}
	}
	else
	{
		sqlite3_bind_int64(sql_stmt_WriteDepthMap_, 1, image.ID);
		sqlite3_bind_int64(sql_stmt_WriteDepthMap_, 2, image.valid);
		sqlite3_bind_int64(sql_stmt_WriteDepthMap_, 3, image.num);
		if (OPT::bCompress)
		{
			sqlite3_bind_blob(sql_stmt_WriteDepthMap_, 4, depthCompress, depthLen, SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_WriteDepthMap_, 5, confCompress, confLen, SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_WriteDepthMap_, 6, normalCompress, normalLen, SQLITE_STATIC);
		}
		else
		{
			sqlite3_bind_blob(sql_stmt_WriteDepthMap_, 4, reinterpret_cast<const char *>(image.depthMap.data), static_cast<int>(CV_ELEM_SIZE(CV_32F) * image.width * image.height), SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_WriteDepthMap_, 5, reinterpret_cast<const char *>(image.confMap.data), static_cast<int>(CV_ELEM_SIZE(CV_32F) * image.width * image.height), SQLITE_STATIC);
			sqlite3_bind_blob(sql_stmt_WriteDepthMap_, 6, reinterpret_cast<const char *>(image.normalMap.data), static_cast<int>(CV_ELEM_SIZE(CV_32FC3) * image.width * image.height), SQLITE_STATIC);
		}
		const int result_code = sqlite3_step(sql_stmt_WriteDepthMap_);
		sqlite3_reset(sql_stmt_WriteDepthMap_);
		std::free(depthCompress);
		std::free(confCompress);
		std::free(normalCompress);
		switch (result_code)
		{
		case SQLITE_OK:
		case SQLITE_ROW:
		case SQLITE_DONE:
			return 1;
		default:
			LOG(ERROR) << "SQLite error: " << sqlite3_errstr(result_code);
			return 0;
		}
	}
}

bool Db::ReadDepthMap(Image& image, bool with_depthmap, bool with_confmap, bool with_normalmap)
{
	sqlite3_bind_int64(sql_stmt_ReadDepthMap_, 1, image.ID);
	const int result_code = sqlite3_step(sql_stmt_ReadDepthMap_);

	if (result_code != SQLITE_ROW)
	{
		sqlite3_reset(sql_stmt_ReadDepthMap_);
		return 0;
	}

	image.valid = sqlite3_column_int64(sql_stmt_ReadDepthMap_, 0);
	image.num = sqlite3_column_int64(sql_stmt_ReadDepthMap_, 1);

	if (with_depthmap && sqlite3_column_type(sql_stmt_ReadDepthMap_, 2) != SQLITE_NULL)
	{
		image.depthMap = cv::Mat(image.height, image.width, CV_32F);
		if (OPT::bCompress)
		{
			int dataLen = sqlite3_column_bytes(sql_stmt_ReadDepthMap_, 2);
			Byte *dataCompress = (Byte *)malloc(sizeof(Byte) * dataLen);
			std::memcpy(dataCompress, (const Byte *)sqlite3_column_blob(sql_stmt_ReadDepthMap_, 2), dataLen);
			uLong dstLen = CV_ELEM_SIZE(CV_32F) * image.width * image.height;

			if (uncompress((Byte *)(image.depthMap.data), &dstLen, dataCompress, dataLen) != Z_OK)
			{
				LOG(ERROR) << "Uncompress data error for image " << image.ID;
				std::free(dataCompress);
				sqlite3_reset(sql_stmt_ReadDepthMap_);
				image.depthMap.release();
				return 0;
			}
			std::free(dataCompress);
		}
		else
			std::memcpy(reinterpret_cast<char *>(image.depthMap.data), sqlite3_column_blob(sql_stmt_ReadDepthMap_, 2), CV_ELEM_SIZE(CV_32F) * image.height * image.width);
	}

	if (with_confmap && sqlite3_column_type(sql_stmt_ReadDepthMap_, 3) != SQLITE_NULL)
	{
		image.confMap = cv::Mat(image.height, image.width, CV_32F);
		if (OPT::bCompress)
		{
			int dataLen = sqlite3_column_bytes(sql_stmt_ReadDepthMap_, 3);
			Byte *dataCompress = (Byte *)malloc(sizeof(Byte) * dataLen);
			std::memcpy(dataCompress, (const Byte *)sqlite3_column_blob(sql_stmt_ReadDepthMap_, 3), dataLen);
			uLong dstLen = CV_ELEM_SIZE(CV_32F) * image.width * image.height;

			if (uncompress((Byte *)(image.confMap.data), &dstLen, dataCompress, dataLen) != Z_OK)
			{
				LOG(ERROR) << "Uncompress data error for image " << image.ID;
				std::free(dataCompress);
				sqlite3_reset(sql_stmt_ReadDepthMap_);
				image.confMap.release();
				return 0;
			}
			std::free(dataCompress);
		}
		else
			std::memcpy(reinterpret_cast<char *>(image.confMap.data), sqlite3_column_blob(sql_stmt_ReadDepthMap_, 3), CV_ELEM_SIZE(CV_32F) * image.height * image.width);
	}

	if (with_normalmap && sqlite3_column_type(sql_stmt_ReadDepthMap_, 4) != SQLITE_NULL)
	{
		image.normalMap = cv::Mat(image.height, image.width, CV_32FC3);
		if (OPT::bCompress)
		{
			int dataLen = sqlite3_column_bytes(sql_stmt_ReadDepthMap_, 4);
			Byte *dataCompress = (Byte *)malloc(sizeof(Byte) * dataLen);
			std::memcpy(dataCompress, (const Byte *)sqlite3_column_blob(sql_stmt_ReadDepthMap_, 4), dataLen);
			uLong dstLen = CV_ELEM_SIZE(CV_32FC3) * image.width * image.height;

			if (uncompress((Byte *)(image.normalMap.data), &dstLen, dataCompress, dataLen) != Z_OK)
			{
				LOG(ERROR) << "Uncompress data error for image " << image.ID;
				std::free(dataCompress);
				sqlite3_reset(sql_stmt_ReadDepthMap_);
				image.normalMap.release();
				return 0;
			}
			std::free(dataCompress);
		}
		else
			std::memcpy(reinterpret_cast<char *>(image.normalMap.data), sqlite3_column_blob(sql_stmt_ReadDepthMap_, 4), CV_ELEM_SIZE(CV_32FC3) * image.height * image.width);
	}

	sqlite3_reset(sql_stmt_ReadDepthMap_);
	return 1;
}

bool Db::CleanMaskFilters()
{
	SQLITE3_EXEC(database_, "DELETE FROM maskfilters;");
	return 1;
}

bool Db::ExistMaskFilter(int image_id)
{
	int result_code;

	sqlite3_bind_int64(sql_stmt_ExistMaskFilter_, 1, image_id);
	result_code = sqlite3_step(sql_stmt_ExistMaskFilter_);
	sqlite3_reset(sql_stmt_ExistMaskFilter_);

	if (result_code != SQLITE_ROW)
		return 0;
	else
		return 1;
}

bool Db::WriteMaskFilter(Image& image)
{
	int image_id = image.ID;
	if (!image.valid || image.maskFilter.empty())
	{
		LOG(INFO) << "Mask filter for image " << image_id << " is invalid";
		return 0;
	}

	uLong filterSrcLen = CV_ELEM_SIZE(IPL_DEPTH_1U) * image.width * image.height;
	uLong filterLen = compressBound(filterSrcLen);
	Byte* filterCompress = (Byte*)malloc(sizeof(Byte) * filterLen);

	if (OPT::bCompress)
	{
		if (compress(filterCompress, &filterLen, (const Byte *)(image.maskFilter.data), filterSrcLen) != Z_OK)
		{
			LOG(ERROR) << "Compress data error for image " << image_id;
			return 0;
		}
		LOG(INFO) << fixed << setprecision(1) << "Compress for image " << image.ID << ": " << (float)filterLen / (float)filterSrcLen * 100.f << "%";
	}

	if (ExistMaskFilter(image_id))
	{
		sqlite3_bind_int64(sql_stmt_UpdateMaskFilter_, 1, image.valid);
		sqlite3_bind_int64(sql_stmt_UpdateMaskFilter_, 2, image.num);
		if (OPT::bCompress)
		{
			sqlite3_bind_blob(sql_stmt_UpdateMaskFilter_, 3, filterCompress, filterLen, SQLITE_STATIC);
		}
		else
		{
			sqlite3_bind_blob(sql_stmt_UpdateMaskFilter_, 3, reinterpret_cast<const char *>(image.maskFilter.data), static_cast<int>(CV_ELEM_SIZE(IPL_DEPTH_1U) * image.width * image.height), SQLITE_STATIC);
		}
		sqlite3_bind_int64(sql_stmt_UpdateMaskFilter_, 4, image_id);

		const int result_code = sqlite3_step(sql_stmt_UpdateMaskFilter_);
		sqlite3_reset(sql_stmt_UpdateMaskFilter_);
		std::free(filterCompress);
		switch (result_code)
		{
		case SQLITE_OK:
		case SQLITE_ROW:
		case SQLITE_DONE:
			return 1;
		default:
			LOG(ERROR) << "SQLite error: " << sqlite3_errstr(result_code);
			return 0;
		}
	}
	else
	{
		sqlite3_bind_int64(sql_stmt_WriteMaskFilter_, 1, image_id);
		sqlite3_bind_int64(sql_stmt_WriteMaskFilter_, 2, image.valid);
		sqlite3_bind_int64(sql_stmt_WriteMaskFilter_, 3, image.num);
		if (OPT::bCompress)
		{
			sqlite3_bind_blob(sql_stmt_WriteMaskFilter_, 4, filterCompress, filterLen, SQLITE_STATIC);
		}
		else
		{
			sqlite3_bind_blob(sql_stmt_WriteMaskFilter_, 4, reinterpret_cast<const char *>(image.maskFilter.data), static_cast<int>(CV_ELEM_SIZE(IPL_DEPTH_1U) * image.width * image.height), SQLITE_STATIC);
		}

		const int result_code = sqlite3_step(sql_stmt_WriteMaskFilter_);
		sqlite3_reset(sql_stmt_WriteMaskFilter_);
		std::free(filterCompress);
		switch (result_code)
		{
		case SQLITE_OK:
		case SQLITE_ROW:
		case SQLITE_DONE:
			return 1;
		default:
			LOG(ERROR) << "SQLite error: " << sqlite3_errstr(result_code);
			return 0;
		}
	}	
}

bool Db::ReadMaskFilter(Image& image)
{
	sqlite3_bind_int64(sql_stmt_ReadMaskFilter_, 1, image.ID);
	const int result_code = sqlite3_step(sql_stmt_ReadMaskFilter_);

	if (result_code != SQLITE_ROW)
	{
		sqlite3_reset(sql_stmt_ReadMaskFilter_);
		return 0;
	}

	image.valid = sqlite3_column_int64(sql_stmt_ReadMaskFilter_, 0);
	image.num = sqlite3_column_int64(sql_stmt_ReadMaskFilter_, 1);	

	if (sqlite3_column_type(sql_stmt_ReadMaskFilter_, 2) != SQLITE_NULL)
	{
		image.maskFilter = cv::Mat(image.height, image.width, IPL_DEPTH_1U);
		if (OPT::bCompress)
		{
			int dataLen = sqlite3_column_bytes(sql_stmt_ReadMaskFilter_, 2);
			Byte *dataCompress = (Byte *)malloc(sizeof(Byte) * dataLen);
			std::memcpy(dataCompress, (const Byte *)sqlite3_column_blob(sql_stmt_ReadMaskFilter_, 2), dataLen);
			uLong dstLen = CV_ELEM_SIZE(IPL_DEPTH_1U) * image.width * image.height;

			if (uncompress((Byte *)(image.maskFilter.data), &dstLen, dataCompress, dataLen) != Z_OK)
			{
				LOG(ERROR) << "Uncompress data error for image " << image.ID;
				std::free(dataCompress);
				sqlite3_reset(sql_stmt_ReadMaskFilter_);
				image.maskFilter.release();
				return 0;
			}
			std::free(dataCompress);
		}
		else
			std::memcpy(reinterpret_cast<char *>(image.maskFilter.data), sqlite3_column_blob(sql_stmt_ReadMaskFilter_, 2), CV_ELEM_SIZE(IPL_DEPTH_1U) * image.height * image.width);
	}

	sqlite3_reset(sql_stmt_ReadMaskFilter_);
	return 1;
}

unsigned long long Db::CountMaskFiltersNum()
{
	string sql = "SELECT SUM(num) FROM maskfilters";
	sqlite3_stmt* sql_stmt;
	sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0);

	if (sqlite3_step(sql_stmt) == SQLITE_ROW)
		return sqlite3_column_int64(sql_stmt, 0);
	else
		return 0;
}
